#!/bin/bash

# MySQL高可用环境搭建脚本
# 作者: MySQL Demo
# 版本: 1.0

set -e

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# 日志函数
log_info() {
    echo -e "${BLUE}[INFO]${NC} $1"
}

log_success() {
    echo -e "${GREEN}[SUCCESS]${NC} $1"
}

log_warning() {
    echo -e "${YELLOW}[WARNING]${NC} $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

# 检查Docker是否安装
check_docker() {
    if ! command -v docker &> /dev/null; then
        log_error "Docker未安装，请先安装Docker"
        exit 1
    fi
    
    if ! command -v docker-compose &> /dev/null; then
        log_error "Docker Compose未安装，请先安装Docker Compose"
        exit 1
    fi
    
    log_success "Docker环境检查通过"
}

# 创建必要的目录
create_directories() {
    log_info "创建必要的目录结构..."
    
    mkdir -p mysql-data/{master,slave1,slave2,backup}
    mkdir -p mysql-config/{master,slave1,slave2,backup}
    mkdir -p mysql-logs/{master,slave1,slave2,backup}
    mkdir -p mysql-router
    mkdir -p scripts
    mkdir -p backup
    
    log_success "目录结构创建完成"
}

# 创建Docker网络
create_docker_network() {
    log_info "创建Docker网络..."
    
    # 检查网络是否已存在
    if docker network ls | grep -q "mysql-cluster"; then
        log_info "网络 mysql-cluster 已存在"
    else
        # 创建网络
        if docker network create mysql-cluster; then
            log_success "网络 mysql-cluster 创建成功"
        else
            log_error "网络 mysql-cluster 创建失败"
            exit 1
        fi
    fi
    
    # 显示网络信息
    log_info "网络信息:"
    docker network ls | grep mysql-cluster
    docker network inspect mysql-cluster --format='{{.Name}}: {{.IPAM.Config}}' 2>/dev/null || true
}

# 创建MySQL配置文件
create_mysql_configs() {
    log_info "创建MySQL配置文件..."
    
    # 主库配置
    cat > mysql-config/master/my.cnf << 'EOF'
[mysqld]
server-id = 1
port = 3306
bind-address = 0.0.0.0

# 二进制日志配置
log-bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
expire_logs_days = 7
max_binlog_size = 100M

# 同步配置
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 性能优化
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_log_buffer_size = 16M

# GTID配置
gtid_mode = ON
enforce_gtid_consistency = ON

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 错误日志
log-error = /var/log/mysql/error.log
log-error-verbosity = 3
EOF

    # 从库1配置
    cat > mysql-config/slave1/my.cnf << 'EOF'
[mysqld]
server-id = 2
port = 3306
bind-address = 0.0.0.0

# 认证插件配置
default_authentication_plugin = mysql_native_password

# 二进制日志配置（可选，用于级联复制）
log-bin = mysql-bin
binlog_format = ROW

# 中继日志配置
relay-log = mysql-relay-bin
relay-log-index = mysql-relay-bin.index
relay-log-info-file = mysql-relay-bin.info

# 同步配置 - 初始化时不设置只读，配置完成后再设置
# read_only = 1
# super_read_only = 1

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 性能优化
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_log_buffer_size = 16M

# GTID配置
gtid_mode = ON
enforce_gtid_consistency = ON

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 错误日志
log-error = /var/log/mysql/error.log
log-error-verbosity = 3
EOF

    # 从库2配置
    cat > mysql-config/slave2/my.cnf << 'EOF'
[mysqld]
server-id = 3
port = 3306
bind-address = 0.0.0.0

# 认证插件配置
default_authentication_plugin = mysql_native_password

# 二进制日志配置（可选，用于级联复制）
log-bin = mysql-bin
binlog_format = ROW

# 中继日志配置
relay-log = mysql-relay-bin
relay-log-index = mysql-relay-bin.index
relay-log-info-file = mysql-relay-bin.info

# 同步配置 - 初始化时不设置只读，配置完成后再设置
# read_only = 1
# super_read_only = 1

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 性能优化
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_log_buffer_size = 16M

# GTID配置
gtid_mode = ON
enforce_gtid_consistency = ON

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 错误日志
log-error = /var/log/mysql/error.log
log-error-verbosity = 3
EOF

    # 备库配置
    cat > mysql-config/backup/my.cnf << 'EOF'
[mysqld]
server-id = 4
port = 3306
bind-address = 0.0.0.0

# 认证插件配置
default_authentication_plugin = mysql_native_password

# 二进制日志配置
log-bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL

# 中继日志配置
relay-log = mysql-relay-bin
relay-log-index = mysql-relay-bin.index
relay-log-info-file = mysql-relay-bin.info

# 同步配置 - 初始化时不设置只读，配置完成后再设置
# read_only = 1
# super_read_only = 1

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 性能优化
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_log_buffer_size = 16M

# GTID配置
gtid_mode = ON
enforce_gtid_consistency = ON

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 错误日志
log-error = /var/log/mysql/error.log
log-error-verbosity = 3
EOF

    log_success "MySQL配置文件创建完成"
}

# 启动MySQL集群
start_mysql_cluster() {
    log_info "启动MySQL集群..."
    
    docker-compose -f docker-compose-ha.yml up -d
    
    log_success "MySQL集群启动完成"
}

# 等待MySQL服务就绪
wait_for_mysql() {
    log_info "等待MySQL服务就绪..."
    
    local max_attempts=30
    local attempt=1
    
    while [ $attempt -le $max_attempts ]; do
        if docker exec mysql-master mysqladmin ping -h 127.0.0.1 -P 3306 -u root -proot123 --silent; then
            log_success "主库服务就绪"
            break
        fi
        
        log_info "等待主库服务就绪... (尝试 $attempt/$max_attempts)"
        sleep 10
        attempt=$((attempt + 1))
    done
    
    if [ $attempt -gt $max_attempts ]; then
        log_error "主库服务启动超时"
        exit 1
    fi
    
    # 等待从库服务就绪
    for slave in mysql-slave1 mysql-slave2 mysql-backup; do
        attempt=1
        while [ $attempt -le $max_attempts ]; do
            if docker exec $slave mysqladmin ping -h 127.0.0.1 -P 3306 -u root -proot123 --silent; then
                log_success "$slave 服务就绪"
                break
            fi
            
            log_info "等待 $slave 服务就绪... (尝试 $attempt/$max_attempts)"
            sleep 10
            attempt=$((attempt + 1))
        done
        
        if [ $attempt -gt $max_attempts ]; then
            log_error "$slave 服务启动超时"
            exit 1
        fi
    done
}

# 配置主从复制
setup_replication() {
    log_info "配置主从复制..."
    
    # 在主库创建复制用户
    docker exec mysql-master mysql -h 127.0.0.1 -P 3306 -u root -proot123 -e "
        CREATE USER IF NOT EXISTS 'repl'@'%' IDENTIFIED BY 'repl123';
        GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
        FLUSH PRIVILEGES;
    "
    
    # 获取主库状态
    local master_status=$(docker exec mysql-master mysql -h 127.0.0.1 -P 3306 -u root -proot123 -e "SHOW MASTER STATUS\G" | grep -E "(File|Position)")
    log_info "主库状态: $master_status"
    
    # 配置从库1
    docker exec mysql-slave1 mysql -h 127.0.0.1 -P 3306 -u root -proot123 -e "
        STOP REPLICA;
        RESET REPLICA ALL;
        CHANGE REPLICATION SOURCE TO
            SOURCE_HOST = 'mysql-master',
            SOURCE_PORT = 3306,
            SOURCE_USER = 'repl',
            SOURCE_PASSWORD = 'repl123',
            SOURCE_AUTO_POSITION = 1;
        START REPLICA;
    "
    
    # 配置从库2
    docker exec mysql-slave2 mysql -h 127.0.0.1 -P 3306 -u root -proot123 -e "
        STOP REPLICA;
        RESET REPLICA ALL;
        CHANGE REPLICATION SOURCE TO
            SOURCE_HOST = 'mysql-master',
            SOURCE_PORT = 3306,
            SOURCE_USER = 'repl',
            SOURCE_PASSWORD = 'repl123',
            SOURCE_AUTO_POSITION = 1;
        START REPLICA;
    "
    
    # 配置备库
    docker exec mysql-backup mysql -h 127.0.0.1 -P 3306 -u root -proot123 -e "
        STOP REPLICA;
        RESET REPLICA ALL;
        CHANGE REPLICATION SOURCE TO
            SOURCE_HOST = 'mysql-master',
            SOURCE_PORT = 3306,
            SOURCE_USER = 'repl',
            SOURCE_PASSWORD = 'repl123',
            SOURCE_AUTO_POSITION = 1;
        START REPLICA;
    "
    
    # 配置完成后启用只读模式
    log_info "启用从库只读模式..."
    for slave in mysql-slave1 mysql-slave2 mysql-backup; do
        docker exec $slave mysql -h 127.0.0.1 -P 3306 -u root -proot123 -e "
            SET GLOBAL read_only = 1;
            SET GLOBAL super_read_only = 1;
        "
        log_success "$slave 只读模式已启用"
    done
    
    log_success "主从复制配置完成"
}

# 检查MySQL服务状态
check_mysql_status() {
    log_info "检查MySQL服务状态..."
    
    local all_healthy=true
    
    # 检查主库状态
    log_info "检查主库状态..."
    if docker exec mysql-master mysqladmin ping -h 127.0.0.1 -P 3306 -u root -proot123 --silent; then
        log_success "主库服务状态正常"
        
        # 检查主库GTID状态
        local master_gtid=$(docker exec mysql-master mysql -h 127.0.0.1 -P 3306 -u root -proot123 -e "SELECT @@GLOBAL.GTID_EXECUTED;" -s -N)
        log_info "主库GTID状态: $master_gtid"
        
        # 检查主库二进制日志状态
        local master_binlog=$(docker exec mysql-master mysql -h 127.0.0.1 -P 3306 -u root -proot123 -e "SHOW MASTER STATUS\G" | grep -E "(File|Position)" | head -2)
        log_info "主库二进制日志状态: $master_binlog"
    else
        log_error "主库服务状态异常"
        all_healthy=false
    fi
    
    # 检查从库状态
    for slave in mysql-slave1 mysql-slave2 mysql-backup; do
        log_info "检查 $slave 状态..."
        if docker exec $slave mysqladmin ping -h 127.0.0.1 -P 3306 -u root -proot123 --silent; then
            log_success "$slave 服务状态正常"
            
            # 检查从库复制状态
            local replica_status=$(docker exec $slave mysql -h 127.0.0.1 -P 3306 -u root -proot123 -e "SHOW REPLICA STATUS\G" | grep -E "(Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Master)" | head -3)
            log_info "$slave 复制状态: $replica_status"
            
            # 检查从库GTID状态
            local slave_gtid=$(docker exec $slave mysql -h 127.0.0.1 -P 3306 -u root -proot123 -e "SELECT @@GLOBAL.GTID_EXECUTED;" -s -N)
            log_info "$slave GTID状态: $slave_gtid"
            
            # 检查从库只读状态
            local read_only=$(docker exec $slave mysql -h 127.0.0.1 -P 3306 -u root -proot123 -e "SELECT @@GLOBAL.read_only, @@GLOBAL.super_read_only;" -s -N)
            log_info "$slave 只读状态: $read_only"
        else
            log_error "$slave 服务状态异常"
            all_healthy=false
        fi
    done
    
    if [ "$all_healthy" = true ]; then
        log_success "所有MySQL服务状态检查完成，状态正常"
    else
        log_error "部分MySQL服务状态异常，请检查日志"
        return 1
    fi
}

# 验证复制状态
verify_replication() {
    log_info "验证复制状态..."
    
    # 在主库创建测试数据
    docker exec mysql-master mysql -h 127.0.0.1 -P 3306 -u root -proot123 testdb -e "
        CREATE TABLE IF NOT EXISTS test_table (
            id INT PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(50),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        INSERT INTO test_table (name) VALUES ('test_$(date +%s)');
    "
    
    # 等待复制完成
    sleep 15
    
    # 验证从库数据
    for slave in mysql-slave1 mysql-slave2 mysql-backup; do
        local count=$(docker exec $slave mysql -h 127.0.0.1 -P 3306 -u root -proot123 testdb -e "SELECT COUNT(*) FROM test_table;" -s -N)
        if [ "$count" -gt 0 ]; then
            log_success "$slave 复制正常，数据条数: $count"
        else
            log_error "$slave 复制异常"
        fi
    done
    
    log_success "复制状态验证完成"
}

# 创建监控脚本
create_monitoring_scripts() {
    log_info "创建监控脚本..."
    
    # 复制状态检查脚本
    cat > scripts/check_replication.sh << 'EOF'
#!/bin/bash

# 复制状态检查脚本

MASTER_HOST="mysql-master"
SLAVE_HOSTS=("mysql-slave1" "mysql-slave2" "mysql-backup")

echo "=== MySQL复制状态检查 ==="
echo "检查时间: $(date)"
echo

# 检查主库状态
echo "主库状态:"
docker exec $MASTER_HOST mysql -u root -proot123 -e "SHOW MASTER STATUS\G" | grep -E "(File|Position|Executed_Gtid_Set)"

echo

# 检查从库状态
for slave in "${SLAVE_HOSTS[@]}"; do
    echo "从库 $slave 状态:"
    docker exec $slave mysql -u root -proot123 -e "SHOW REPLICA STATUS\G" | grep -E "(Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Master|Last_Error)"
    echo
done
EOF

    # 性能监控脚本
    cat > scripts/monitor_performance.sh << 'EOF'
#!/bin/bash

# MySQL性能监控脚本

MASTER_HOST="mysql-master"

echo "=== MySQL性能监控 ==="
echo "检查时间: $(date)"
echo

# 连接数
echo "当前连接数:"
docker exec $MASTER_HOST mysql -u root -proot123 -e "SHOW STATUS LIKE 'Threads_connected';"

# 查询统计
echo -e "\n查询统计:"
docker exec $MASTER_HOST mysql -u root -proot123 -e "SHOW STATUS LIKE 'Questions';"
docker exec $MASTER_HOST mysql -u root -proot123 -e "SHOW STATUS LIKE 'Slow_queries';"

# InnoDB状态
echo -e "\nInnoDB状态:"
docker exec $MASTER_HOST mysql -u root -proot123 -e "SHOW ENGINE INNODB STATUS\G" | grep -E "(Buffer pool hit rate|Log sequence number|Last checkpoint at)"
EOF

    # 备份脚本
    cat > scripts/backup.sh << 'EOF'
#!/bin/bash

# MySQL备份脚本

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="./backup"
MASTER_HOST="mysql-master"
MASTER_PORT=3306

# 创建备份目录
mkdir -p $BACKUP_DIR

echo "开始备份 MySQL 数据库..."
echo "备份时间: $DATE"
echo "备份目录: $BACKUP_DIR"

# 全量备份
docker exec $MASTER_HOST mysqldump -h localhost -P 3306 -u root -proot123 \
    --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    --gtid-purged=OFF \
    > $BACKUP_DIR/full_backup_$DATE.sql

if [ $? -eq 0 ]; then
    # 压缩备份文件
    gzip $BACKUP_DIR/full_backup_$DATE.sql
    
    # 删除7天前的备份
    find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
    
    echo "备份完成: $BACKUP_DIR/full_backup_$DATE.sql.gz"
    echo "备份大小: $(du -h $BACKUP_DIR/full_backup_$DATE.sql.gz | cut -f1)"
else
    echo "备份失败!"
    exit 1
fi
EOF

    # 故障切换脚本
    cat > scripts/failover.sh << 'EOF'
#!/bin/bash

# MySQL故障切换脚本

MASTER_HOST="mysql-master"
BACKUP_HOST="mysql-backup"
MASTER_PORT=3306
BACKUP_PORT=3306

LOG_FILE="./failover.log"

log() {
    echo "$(date): $1" | tee -a $LOG_FILE
}

# 检查主库状态
check_master() {
    docker exec $MASTER_HOST mysqladmin ping -h localhost -u root -proot123 --silent > /dev/null 2>&1
    return $?
}

# 检查备库状态
check_backup() {
    docker exec $BACKUP_HOST mysqladmin ping -h localhost -u root -proot123 --silent > /dev/null 2>&1
    return $?
}

# 主库故障时切换到备库
if ! check_master; then
    log "主库故障，开始故障切换..."
    
    if check_backup; then
        # 停止备库复制
        docker exec $BACKUP_HOST mysql -u root -proot123 -e "STOP REPLICA;"
        
        # 设置备库为可写
        docker exec $BACKUP_HOST mysql -u root -proot123 -e "SET GLOBAL read_only = 0; SET GLOBAL super_read_only = 0;"
        
        log "故障切换完成，备库已提升为主库"
        
        # 发送告警（可以集成邮件、短信等）
        echo "MySQL主库故障，已自动切换到备库" | mail -s "MySQL故障切换告警" admin@example.com
    else
        log "备库也故障，无法进行故障切换"
        exit 1
    fi
else
    log "主库状态正常"
fi
EOF

    # 设置脚本权限
    chmod +x scripts/*.sh
    
    log_success "监控脚本创建完成"
}

# 显示使用说明
show_usage() {
    log_info "MySQL高可用环境搭建完成！"
    echo
    echo "=== 使用说明 ==="
    echo "1. 启动集群: docker-compose -f docker-compose-ha.yml up -d"
    echo "2. 停止集群: docker-compose -f docker-compose-ha.yml down"
    echo "3. 查看日志: docker-compose -f docker-compose-ha.yml logs -f"
    echo "4. 检查复制状态: ./scripts/check_replication.sh"
    echo "5. 性能监控: ./scripts/monitor_performance.sh"
    echo "6. 数据备份: ./scripts/backup.sh"
    echo "7. 故障切换: ./scripts/failover.sh"
    echo
    echo "=== 连接信息 ==="
    echo "主库: localhost:3301 (root/root123)"
    echo "从库1: localhost:3307 (root/root123)"
    echo "从库2: localhost:3308 (root/root123)"
    echo "备库: localhost:3309 (root/root123)"
    echo "应用用户: appuser/app123"
    echo
    echo "=== 测试连接 ==="
    echo "mysql -h localhost -P 3301 -u root -p"
    echo "mysql -h localhost -P 3307 -u root -p"
    echo "mysql -h localhost -P 3308 -u root -p"
    echo "mysql -h localhost -P 3309 -u root -p"
}

# 主函数
main() {
    echo "=========================================="
    echo "    MySQL高可用环境搭建脚本"
    echo "=========================================="
    echo
    
    check_docker
    create_directories
    create_docker_network
    create_mysql_configs
    start_mysql_cluster
    wait_for_mysql
    setup_replication
    check_mysql_status
    verify_replication
    create_monitoring_scripts
    show_usage
    
    echo
    log_success "MySQL高可用环境搭建完成！"
}

# 执行主函数
main "$@"
